Imports System.Data.SqlClient

Namespace Database

    Public Class cDataBase

        Private ConnectionCount As Integer

        Public Event WriteConsoleLine(ByVal strText As String)

        Dim mConnection() As SqlConnection
        Dim mConnectionBusy() As Boolean
        Dim mBlockGetFreeConnection As Boolean

        Dim mSource As String
        Dim mUser As String
        Dim mPass As String

        Public Sub New(ByVal source As String, ByVal user As String, ByVal pass As String, Optional ByVal connections As Integer = 8)

            mSource = source
            mUser = user
            mPass = pass

            Dim i As Integer

            ConnectionCount = connections
            ReDim mConnection(ConnectionCount)
            ReDim mConnectionBusy(ConnectionCount - 1)

            For i = 0 To ConnectionCount


                mConnection(i) = New SqlConnection("Data Source=" & source & ";Uid=" & user & ";Pwd=" & pass & ";")
                mConnection(i).Open()
                If i < ConnectionCount Then mConnectionBusy(i) = False

            Next

        End Sub

        Public Sub Shutdown()

            For i As Integer = 0 To ConnectionCount

                mConnection(i).Close()

            Next

        End Sub

        Public Sub UseDatabase(ByVal dbname As String)

            For i As Integer = 0 To ConnectionCount

                mConnection(i).ChangeDatabase(dbname)

            Next

        End Sub

        Private Function GetUnBlockingConnection() As Integer
            Return ConnectionCount
        End Function

        Private Function GetFreeConnection() As Integer

            Do Until Not mBlockGetFreeConnection
                System.Windows.Forms.Application.DoEvents()
                System.Threading.Thread.Sleep(10)
            Loop

            mBlockGetFreeConnection = True

            'obtain a free connection, block it and return index
            Dim i As Integer

            Do
                For i = 0 To ConnectionCount - 1
                    If Not mConnectionBusy(i) Then
                        mConnectionBusy(i) = True

                        If mConnection(i).State = ConnectionState.Closed Then
                            mConnection(i) = New SqlConnection("Data Source=" & msource & ";Uid=" & muser & ";Pwd=" & mpass & ";")
                            mConnection(i).Open()
                        End If

                        Exit Do
                    End If
                Next
                System.Windows.Forms.Application.DoEvents()
            Loop

            mBlockGetFreeConnection = False

            Return i

        End Function

        Private Sub UnBlockConnection(ByVal index As Integer)

            mConnectionBusy(index) = False

        End Sub

        Public Sub CheckDatabase(ByVal dbname As String)

            Dim mExists As Boolean = False
            Dim connindex As Integer = GetFreeConnection()
            Dim mCmd As SqlCommand

            Try
                mCmd = New SqlCommand("SELECT name FROM master..sysdatabases;", mConnection(connindex))
                Dim mReader As SqlDataReader = mCmd.ExecuteReader()

                Do Until Not mReader.Read
                    If mReader.GetString(0) = dbname Then
                        mExists = True
                        Exit Do
                    End If
                Loop
                mReader.Close()
                mCmd.Dispose()
            Catch
                RaiseEvent WriteConsoleLine("Sql DB " & dbname & "Check Failed!")
            End Try

            UnBlockConnection(connindex)
            connindex = GetUnBlockingConnection()

            If Not mExists Then
                Try
                    mCmd = New SqlCommand("CREATE DATABASE " & dbname & ";", mConnection(connindex))
                    mCmd.ExecuteNonQuery()
                    mCmd.Dispose()
                    RaiseEvent WriteConsoleLine("Sql DB " & dbname & " Created")
                Catch e As Exception
                    RaiseEvent WriteConsoleLine("Sql DB " & dbname & " Creation Failed! (" & e.Message & ")")
                End Try
            Else
                RaiseEvent WriteConsoleLine("Sql DB " & dbname & " Exists")
            End If

        End Sub

        Public Function CheckTable(ByVal tablename As String, ByVal paramstring As String) As Boolean

            Dim connindex As Integer = GetFreeConnection()
            Dim mExists As Boolean = False
            Dim mCmd As SqlCommand


            Try
                mCmd = New SqlCommand("SELECT name FROM sys.tables;", mConnection(connindex))
                Dim mReader As SqlDataReader = mCmd.ExecuteReader()

                Do Until Not mReader.Read
                    If mReader.GetString(0) = tablename Then
                        mExists = True
                        Exit Do
                    End If
                Loop
                mReader.Close()
                mCmd.Dispose()
            Catch
                RaiseEvent WriteConsoleLine("Check Table " & tablename & " Failed!")
                Return False
            End Try

            UnBlockConnection(connindex)
            connindex = GetUnBlockingConnection()

            If Not mExists Then
                Try
                    mCmd = New SqlCommand("CREATE TABLE " & tablename & " ( " & paramstring & " );", mConnection(connindex))
                    mCmd.ExecuteNonQuery()
                    mCmd.Dispose()
                    RaiseEvent WriteConsoleLine("Sql Table " & tablename & " Created")
                    Return True
                Catch e As Exception
                    RaiseEvent WriteConsoleLine("Sql Table " & tablename & " Creation Failed! (" & e.Message & ")")
                    Return False
                End Try
            Else
                RaiseEvent WriteConsoleLine("Sql Table " & tablename & " Exists")
                Return False
            End If

        End Function

        Public Function NewRecord(ByVal tablename As String, ByVal columns() As String, ByVal values() As String) As Int64

            Dim i As Integer
            Dim columnstr As String = columns(0)
            Dim valuestr As String = values(0)

            If values.GetUpperBound(0) > 0 Then
                For i = 1 To values.GetUpperBound(0)

                    columnstr &= ", " & columns(i)
                    valuestr &= ", " & values(i)

                Next
            End If

            Dim sSql As String = ("INSERT INTO " & tablename & " (" & columnstr & ") VALUES (" & valuestr & ");  SELECT SCOPE_IDENTITY();")
            Dim row As Collection = ExecuteQueryFirstRecord(sSql)

            If row Is Nothing Then Return 0

            If row.Count = 0 Then
                Return 0
            Else
                If row(1) Is DBNull.Value Then
                    Return 0
                Else
                    Return row(1)
                End If
            End If

        End Function

        'TODO: Change database results to a key-value collection ( arr(0) -> arr("charind") )

        Public Function ExecuteQueryFirstRecord(ByVal sqlstr As String) As Collection

            Dim connindex As Integer = GetFreeConnection()
            Dim mCmd As SqlCommand
            Dim result As Collection = Nothing

            Try

                mCmd = New SqlCommand(sqlstr, mConnection(connindex))
                Dim mReader As SqlDataReader = mCmd.ExecuteReader()
                Dim i As Integer
                result = New Collection

                If mReader.Read Then

                    For i = 0 To mReader.FieldCount - 1
                        If Not result.Contains(mReader.GetName(i)) Then
                            result.Add(mReader.GetValue(i), mReader.GetName(i))
                        End If
                    Next

                End If

                mReader.Close()
                mCmd.Dispose()

            Catch e As Exception
                RaiseEvent WriteConsoleLine("Query " & sqlstr & " Failed! (" & e.Message & ")")
            End Try

            UnBlockConnection(connindex)
            Return result

        End Function

        Public Function ExecuteQuery(ByVal sqlstr As String) As ArrayList

            Dim connindex As Integer = GetFreeConnection()
            Dim mCmd As SqlCommand
            Dim result As ArrayList = Nothing
            Dim rowdata As Collection

            Try

                mCmd = New SqlCommand(sqlstr, mConnection(connindex))
                Dim mReader As SqlDataReader = mCmd.ExecuteReader()
                Dim i As Integer
                result = New ArrayList

                Do Until Not mReader.Read

                    rowdata = New Collection
                    For i = 0 To mReader.FieldCount - 1
                        If Not rowdata.Contains(mReader.GetName(i)) Then
                            rowdata.Add(mReader.GetValue(i), mReader.GetName(i))
                        End If
                    Next
                    result.Add(rowdata)

                Loop

                mReader.Close()
                mCmd.Dispose()

            Catch e As Exception
                RaiseEvent WriteConsoleLine("Query " & sqlstr & " Failed! (" & e.Message & ")")
            End Try

            UnBlockConnection(connindex)
            Return result

        End Function

        Public Function ExecuteNonQuery(ByVal sqlstr As String) As Integer

            Dim connindex As Integer = GetUnBlockingConnection()
            Dim mCmd As SqlCommand
            Dim result As Integer

            Try

                mCmd = New SqlCommand(sqlstr, mConnection(connindex))
                result = mCmd.ExecuteNonQuery()

                mCmd.Dispose()

            Catch e As Exception
                RaiseEvent WriteConsoleLine("NonQuery " & sqlstr & " Failed! (" & e.Message & ")")
            End Try

            Return result

        End Function

    End Class

End Namespace